package il.ac.braude.sona.ocsf.server;

import il.ac.braude.sona.database.ChangeRequest;
import il.ac.braude.sona.database.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DatabaseController {
	private static DatabaseController instance;
	private Connection conn;
	private String dbName = "BraudeSystem";
	String useQuery = "USE " + dbName;
	private static final String connectURL = "jdbc:mysql://0.0.0.0:3306/?user=root";

	private DatabaseController() {
	}

	static DatabaseController getInstance() {
		return instance == null ? instance = new DatabaseController() : instance;
	}

	public User login(ArrayList<String> values) throws SQLException {
		return login(values.get(0), values.get(1));
	}

	public User login(String uid, String pw) throws SQLException {
		Statement stmt = null;
		String query = "select uid, permissions "
				+ "from Users natural join Type" + " where uid=\"" + uid + "\""
				+ " and password=\"" + pw + "\"";
		ResultSet rs = null;
		try {
			Connection conn = DriverManager.getConnection(connectURL);
			stmt = conn.createStatement();
			stmt.execute(useQuery);
			rs = stmt.executeQuery(query);
			if (!rs.next())
				return null;
			return new User(rs.getString("uid"));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null)
				conn.close();
		}
		return null;
	}

	public long createRequest(ArrayList<String> values) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "INSERT INTO  change_request (title ,comment) VALUES ( '"
				+ values.get(0);
		for (int i = 1; i < values.size(); i++)
			query += "','" + values.get(i);
		query += "');";
		try {
			statement = conn.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query,
					Statement.RETURN_GENERATED_KEYS);
			if (affectedRows == 0)
				throw new SQLException(
						"Creating user failed, no rows affected.");
			generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				return generatedKeys.getLong(1);
			} else {
				throw new SQLException(
						"Creating user failed, no generated key obtained.");
			}
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}

	public ChangeRequest getRequestDetails(long rid) {
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "select rid,title,comment " + "from change_request"
				+ " where rid=\"" + rid + "\"";
		try {
			Connection conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			if (!rs.next())
				return null;
			return new ChangeRequest(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return null;
	}

	public ArrayList<ChangeRequest> getAllRequestDetails() {

		PreparedStatement statement = null;
		String query = "select * from change_request";
		ArrayList<ChangeRequest> res = new ArrayList<ChangeRequest>();
		try {
			Connection conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			while (rs.next())
				res.add(new ChangeRequest(rs));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}

}
